package cn.edu.glut.choosethesis.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import cn.edu.glut.entity.Selection;
import cn.edu.glut.vo.SelectionToStudentVO;
/**
 * 
 * @author Caizhfy
 * @version v.0.1
 * @createTime 2017年12月18日下午4:31:23
 * @email Caizhfy@163.com
 * @description
 *
 */
public interface SelectionRepository extends CrudRepository<Selection, String>{

	/**
	 * 
	 * @param status		选题状态
	 * @param studentId		学生id
	 * @param subjectId		题目id
	 * @param successTime	成功时间
	 */
	@Modifying
	@Query(value = "update selection set is_select=:status, success_time=:successTime where student_id=:studentId and subject_id=:subjectId", nativeQuery = true)
	void updateIsSelectByStudentIdAndSubjectId(@Param("status")int status,@Param("studentId")String studentId, 
			@Param("subjectId")String subjectId, @Param("successTime")Date successTime);

	@Query("select se from Selection se where se.subject.id=:subjectId and se.student.id!=:studentId")
	List<Selection> findAllBySubjectIdAndNotIsStudentId(@Param("subjectId")String subjectId, @Param("studentId")String studentId);

	//可使用SpringData名字查询
	@Query("select count(se.id) from Selection se where se.student.id=:studentId")
	long countByStudentId(@Param("studentId")String studentId);
	
	@Query("select count(se.id) from Selection se where se.subject.id=:subjectId")
	long countBySubjectId(String subjectId);
	
	@Query("select count(se.id) from Selection se where se.student.id=:studentId and se.subject.id=:subjectId")
	long countByStudentIdAndSubjectId(@Param("studentId")String studentId, @Param("subjectId")String subjectId);

	@Query("select new cn.edu.glut.vo.SelectionToStudentVO(su.id, su.title, se.wish, se.isSelection) from Selection se left join se.subject su where se.student.id=:studentId")
	List<SelectionToStudentVO> findByStudentId(@Param("studentId")String studentId);

	@Query("select se from Selection se where se.subject.id=:subjectId and se.student.id=:studentId")
	Selection findByStudentIdAndSubjectId(@Param("studentId")String studentId, @Param("subjectId")String subjectId);

	@Query("select case when max(se.isSelection) is null then 0 else max(se.isSelection) end from Selection se where se.subject.id=:subjectId")
	int findMaxIsSelectBySubjectId(@Param("subjectId")String subjectId);
	
	@Query("select case when max(se.isSelection) is null then 0 else max(se.isSelection) end from Selection se where se.student.id=:studentId")
	int findMaxIsSelectByStudentId(@Param("studentId")String studentId);

	@Modifying
	@Query(value = "delete from selection where student_id=:studentId and subject_id=:subjectId", nativeQuery = true)
	void deleteByStudentIdAndSubjectId(@Param("studentId")String studentId, @Param("subjectId")String subjectId);

	
}
